package com.project.dao.impl;

import com.project.bean.Student;
import com.project.dao.IStudentDao;

import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

/**
 * 学生持久接口实现方法
 */
public class StudentDaoImpl implements IStudentDao {
    @Override
    public void add(Student student) {
        Connection con = null;
        PreparedStatement ps = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");  //加载驱动

            /*第一个参数为URL，localhost:12345 表示连接数据库主机的IP地址和端口
              mydb为连接mysql的库名，characterEncoding=utf-8表示设置编码集
              第二个参数为登录mysql的用户名，第三个参数为登录mysql的密码  */
            con = DriverManager.getConnection("jdbc:mysql://localhost:12345/mydb?characterEncoding=utf-8",
                    "root","lovo");  //建立连接
            System.out.println(con);   //测试连接是否成功

            //执行sql语句，其中？表示占位符
            ps = con.prepareStatement
                    ("insert into t_student(s_name,s_sex,s_birthday,s_phone)values (?,?,?,?)");
            /*
             *         填充占位符
             */
            ps.setObject(1,student.getName());
            ps.setObject(2,student.getSex());
            ps.setObject(3,student.getBirthday().toString());
            ps.setObject(4,student.getPhone());

            ps.executeUpdate();  // 更新数据库

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                //关闭连接
                ps.close();
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void del(int id) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");   //加载驱动
            con = DriverManager.getConnection
                    ("jdbc:mysql://localhost:12345/mydb?character=utf-8","root","lovo");
            ps = con.prepareStatement("delete from t_student where pk_studentId=?");
            ps.setObject(1,id);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                con.close();
                ps.close();
            } catch (Exception throwables) {
                throwables.printStackTrace();
            }
        }

    }

    @Override
    public void update(int id, String phone) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:12345/mydb?character:utf-8",
                    "root","lovo");
            ps = con.prepareStatement("update t_student set s_phone=? where pk_studentId=?");
            ps.setObject(1,phone);
            ps.setObject(2,id);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                ps.close();
                con.close();
            } catch (Exception throwables) {
                throwables.printStackTrace();
            }

        }
    }

    @Override
    public List<Student> findAll() {
        List<Student> list = new ArrayList<>();
        Connection con = null;  //连接对象
        PreparedStatement ps = null;  //sql语句执行对象
        ResultSet rs = null;  //结果集
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:12345/mydb?characterEncoding=utf-8",
                    "root","lovo");

            ps = con.prepareStatement("select * from t_student");
            rs = ps.executeQuery();  //得到结果集
            /*遍历结果集，将结果集指针指向下一条记录，如果没有下一条记录，返回false*/
            while (rs.next()){
               //创建学生对象，封装结果集中查询的内容
                Student s = new Student();
                s.setId(rs.getInt("pk_studentId")); //将pk_studentId列的值取出,封装为学生的id属性
                s.setSex(rs.getString("s_sex"));
                s.setName(rs.getString("s_name"));
                s.setBirthday(LocalDate.parse(rs.getString("s_birthday")));
                s.setPhone(rs.getString("s_phone"));

                list.add(s); //学生对象加入集合
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (Exception throwables) {
                throwables.printStackTrace();
            }
        }
        return list;
    }

    @Override
    public List<Student> findName(String name) {
        List<Student> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection
                    ("jdbc:mysql://localhost:12345/mydb?characterEncoding=utf-8","root","lovo");
            ps = con.prepareStatement("select * from t_student where s_name like ?");
            ps.setObject(1,"%"+name+"%");
            rs = ps.executeQuery();
            while (rs.next()){
                Student s = new Student();
                s.setId(rs.getInt("pk_studentId"));
                s.setName(rs.getString("s_name"));
                s.setSex(rs.getString("s_sex"));
                s.setBirthday(LocalDate.parse(rs.getString("s_birthday")));
                s.setPhone(rs.getString("s_phone"));

                list.add(s);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return list;
    }

    @Override
    public List<Student> findByBirthday(LocalDate startDate, LocalDate endDate) {
        List<Student> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection
                    ("jdbc:mysql://localhost:12345/mydb?character=utf-8","root","lovo");
            ps = con.prepareStatement("select * from t_student where s_birthday>=? and s_birthday<=?");
            ps.setObject(1,startDate.toString());
            ps.setObject(2,endDate.toString());

            rs = ps.executeQuery();
            while (rs.next()){
                Student s = new Student();
                s.setId(rs.getInt("pk_studentId"));
                s.setName(rs.getString("s_name"));
                s.setSex(rs.getString("s_sex"));
                s.setBirthday(LocalDate.parse(rs.getString("s_birthday")));
                s.setPhone(rs.getString("s_phone"));

                list.add(s);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return list;
    }

    @Override
    public Student findById(int id) {
        Student s = null;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection
                    ("jdbc:mysql://localhost:12345/mydb?character=utf-8","root","lovo");
            ps = con.prepareStatement("select * from t_student where pk_studentId=?");
            ps.setObject(1,id);
            rs = ps.executeQuery();

            if (rs.next()){
                s = new Student();
                s.setId(rs.getInt("pk_studentId"));
                s.setName(rs.getString("s_name"));
                s.setSex(rs.getString("s_sex"));
                s.setBirthday(LocalDate.parse(rs.getString("s_birthday")));
                s.setPhone(rs.getString("s_phone"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return s;
    }

    public static void main(String[] args) {
        IStudentDao dao = new StudentDaoImpl();
//        dao.add(new Student("刘勇","男", LocalDate.parse("1985-03-09"),"13209097767"));
//        dao.del(6);
//        dao.update(1,"13982889090");
//        System.out.println(dao.findAll());
        System.out.println(dao.findName("张"));
//        System.out.println(dao.findByBirthday(LocalDate.parse("1990-01-01"),LocalDate.parse("1999-12-30")));
//        System.out.println(dao.findById(1));
    }
}
